<?php

namespace Topxia\Service\Zhibo\Dao\Impl;

use Topxia\Service\Common\BaseDao;
use Topxia\Service\Zhibo\Dao\ZhiboDao;
use Topxia\Common\DaoException;
use PDO;

class ZhiboDaoImpl extends BaseDao implements ZhiboDao
{
    protected $table = 'zhibo';

    public function setzhibo($data)
    {
    	$data['time']=time();
        $affected = $this->getConnection()->insert($this->table, $data);
		return $affected;
    }
	/*
	 * 编辑直播
	 */ 
    public function esavezhibo($id,$data){
    	$datas="zblname='".$data['zblname']."', zbjname ='".$data['zbjname']."', image ='".$data['image']."', url ='".$data['url']."', teachers ='".$data['teachers']."', teacher ='".$data['teacher']."', type ='".$data['type']."', zbtime ='".time()."', time ='".time()."', money ='".$data['money']."', notice ='".$data['notice']."'";
    	$sql="UPDATE $this->table SET ".$datas." WHERE id=$id";
		return $this->getConnection()->executeQuery($sql);   
    }
   /*
    * 直播列表
    */
    public function get_zhibo_ss($conditions, $orderBy, $start, $limit)
    {
        $this->filterStartLimit($start, $limit);
        $builder = $this->_createSearchQueryBuilder($conditions)
            ->select('*')
            ->orderBy($orderBy[0], $orderBy[1])
            ->setFirstResult($start)
            ->setMaxResults($limit);
        return $builder->execute()->fetchAll() ? : array(); 
    }
    /*
	 * 统计数量
	 */
	public function get_zhibo_count($conditions)
    {
        $builder = $this->_createSearchQueryBuilder($conditions)
            ->select('COUNT(id)');
        return $builder->execute()->fetchColumn(0);
    } 
	/*
	 * 获取一条记录
	 */ 
	public function get_zhibo_s($id){
		$sql = "SELECT * FROM {$this->table} WHERE id = ? LIMIT 1";
        return $this->getConnection()->fetchAssoc($sql, array($id)) ? : null;
	} 
	 
	 
	 
	 
	 
    protected function _createSearchQueryBuilder($conditions)
    {
       
        $builder = $this->createDynamicQueryBuilder($conditions)
            ->from($this->table, $this->table)
            ->andWhere('id = :id')
            ->andWhere('zblname = :zblname')
            ->andWhere('zbjname = :zbjname')
            ->andWhere('image = :image')
            ->andWhere('url = :url')
            ->andWhere('jianjie = :jianjie')
            ->andWhere('teachers = :teachers')
            ->andWhere('teacher = :teacher')
            ->andWhere('type = :type')
            ->andWhere('zbtime = :zbtime')
            ->andWhere('time = :time')
            ->andWhere('isdisplay = :isdisplay')
            ->andWhere('money = :money')
            ->andWhere('notice = :notice');
        return $builder;
    }
    
    public function updataUservipcourseId($vipnum,$courseId)
    {
       $sql = "UPDATE user_vip_member SET courseId = '$courseId' WHERE vipnum = $vipnum ";
        return $this->getConnection()->executeQuery($sql); 
    } 
	
	public function updataUserlevel($levelnum,$levelname,$color)
    {
       $sql = "UPDATE user_level SET levelname = '$levelname',color='$color' WHERE id = $levelnum ";
        return $this->getConnection()->executeQuery($sql); 
    }
    
	public function updataUservip($vipid,$vipname)
    {
       $sql = "UPDATE user_vip_member SET vip = '$vipname' WHERE id = $vipid ";
        return $this->getConnection()->executeQuery($sql); 
    }
    //签到成功用户修改等级
    public function updateUserdj($userid, $dj)
    {
        $sql = "UPDATE user_profile SET level = '$dj' WHERE id = $userid ";
        //var_dump($sql);exit;
        return $this->getConnection()->executeQuery($sql);  
    }
    
    public function updataUserlevelintegral($id,$integral)
    {
       $sql = "UPDATE user_level SET integral = '$integral' WHERE id = $id ";
        //var_dump($sql);exit;
        return $this->getConnection()->executeQuery($sql); 
    }
    //增加签到记录
    public function addintegral($bind){
        $affected = $this->getConnection()->insert('user_signin', $bind);
         if ($affected <= 0) {
            throw $this->createDaoException('Insert user error.');
        }
        
    }
   
    public function updataUserintegral($signin,$invitation,$beinvitation)
    {
        $sql=" UPDATE user_integral SET signin ='$signin',invitation='$invitation',beinvitation='$beinvitation' WHERE id='1' ";
        
        return $this->getConnection()->executeQuery($sql);
    }
    //查询域名
    public function selectname($username)
    {
        $sql = "SELECT * FROM user_profile WHERE domainname ='$username'";
       
        return $this->getConnection()->fetchAssoc($sql)?: null;
        
    }    
    //查询积分
    public function selectsignin($userid)
    {
        $sql = "SELECT * FROM user_profile WHERE id ='$userid'";
       
        return $this->getConnection()->fetchAll($sql)?: null;
        
    }    
	
	public function selectsigninlist($userid)
    {
        $sql = "SELECT p.truename,u.email,s.* FROM (user_profile p left join user u on p.id=u.id) left join user_signin s on p.id=s.userid where s.userid='$userid'";
       //$sql = "SELECT p.truename,s.* FROM user_profile p left join user_signin s on p.id=s.userid where s.userid='$userid'";
       
        return $this->getConnection()->fetchAll($sql)?: null;
        
    }    
    //修改积分
    public function updatasignin($userid,$integralnum)
    {
        $sql = "UPDATE user_profile SET integral ='$integralnum' WHERE id='$userid'";
       
        return $this->getConnection()->executeQuery($sql)?: null;
        
    }    
    public function getUserlevelname($levelnum)
    {
      $sql = "SELECT * FROM user_level WHERE levelnum = ? LIMIT 1";
        return $this->getConnection()->fetchAssoc($sql,array($levelnum)); 
    }
    
    public function findUserByEmail($email)
    {
        $sql = "SELECT * FROM {$this->table} WHERE email = ? LIMIT 1";
        return $this->getConnection()->fetchAssoc($sql, array($email));
    }

    public function findUserByNickname($nickname)
    {
        $sql = "SELECT * FROM {$this->table} WHERE nickname = ? LIMIT 1";
        return $this->getConnection()->fetchAssoc($sql, array($nickname));
    }

    public function findUserByVerifiedMobile($mobile)
    {
        $sql = "SELECT * FROM {$this->table} WHERE verifiedMobile = ? LIMIT 1";
        return $this->getConnection()->fetchAssoc($sql, array($mobile));
    }

    public function findUsersByNicknames(array $nicknames)
    {
        if(empty($nicknames)) { 
            return array(); 
        }

        $marks = str_repeat('?,', count($nicknames) - 1) . '?';
        $sql ="SELECT * FROM {$this->table} WHERE nickname IN ({$marks});";
        
        return $this->getConnection()->fetchAll($sql, $nicknames);
    }

    public function findUsersByIds(array $ids)
    {
        if(empty($ids)){
            return array();
        }
        $marks = str_repeat('?,', count($ids) - 1) . '?';
        $sql ="SELECT * FROM {$this->table} WHERE id IN ({$marks});";
        
        return $this->getConnection()->fetchAll($sql, $ids);
    }

    public function searchUsers($conditions, $orderBy, $start, $limit)
    {
       
        $this->filterStartLimit($start, $limit);
        $builder = $this->createUserQueryBuilder($conditions)
            ->select('*')
            ->orderBy($orderBy[0], $orderBy[1])
            ->setFirstResult($start)
            ->setMaxResults($limit);
        return $builder->execute()->fetchAll() ? : array();
    }

    public function searchUserCount($conditions)
    {
        $builder = $this->createUserQueryBuilder($conditions)
            ->select('COUNT(id)');
        return $builder->execute()->fetchColumn(0);
    }

    protected function createUserQueryBuilder($conditions)
    {
        $conditions = array_filter($conditions,function($v){
            if($v === 0){
                return true;
            }
                
            if(empty($v)){
                return false;
            }
            return true;
        });
        if (isset($conditions['roles'])) {
            $conditions['roles'] = "%{$conditions['roles']}%";
        }

        if (isset($conditions['role'])) {
            $conditions['role'] = "|{$conditions['role']}|";
        }

        if(isset($conditions['keywordType']) && isset($conditions['keyword'])) {
            $conditions[$conditions['keywordType']]=$conditions['keyword'];
            unset($conditions['keywordType']);
            unset($conditions['keyword']);
        }

        if (isset($conditions['keywordUserType'])) {
            $conditions['type'] = "%{$conditions['keywordUserType']}%";
            unset($conditions['keywordUserType']);
        }

        if (isset($conditions['nickname'])) {
            $conditions['nickname'] = "%{$conditions['nickname']}%";
        }

        if(!empty($conditions['datePicker'])&& $conditions['datePicker'] == 'longinDate'){
            if(isset($conditions['startDate'])){
                $conditions['loginStartTime'] = strtotime($conditions['startDate']);
            }
            if(isset($conditions['endDate'])){
                $conditions['loginEndTime'] = strtotime($conditions['endDate']);
            }
        }
        if(!empty($conditions['datePicker'])&& $conditions['datePicker'] == 'registerDate'){
            if(isset($conditions['startDate'])){
                $conditions['startTime'] = strtotime($conditions['startDate']);
            }
            if(isset($conditions['endDate'])){
                $conditions['endTime'] = strtotime($conditions['endDate']);
            }
        }

        
        $conditions['verifiedMobileNull'] = "";

        $builder = $this->createDynamicQueryBuilder($conditions)
            ->from($this->table, 'user')
            ->andWhere('promoted = :promoted')
            ->andWhere('roles LIKE :roles')
            ->andWhere('roles = :role')
            ->andWhere('level = :level')
            ->andWhere('UPPER(nickname) LIKE :nickname')
            ->andWhere('loginIp = :loginIp')
            ->andWhere('createdIp = :createdIp')
            ->andWhere('approvalStatus = :approvalStatus')
            ->andWhere('email = :email')
            ->andWhere('level = :level')
            ->andWhere('vip = :vip')
            ->andWhere('vipdate = :vipdate')
            ->andWhere('vipenddate = :vipenddate')
            ->andWhere('domainname = :domainname')
            ->andWhere('createdTime >= :startTime')
            ->andWhere('createdTime <= :endTime')
            ->andWhere('approvalTime >= :startApprovalTime')
            ->andWhere('approvalTime <= :endApprovalTime')
            ->andWhere('loginTime >= :loginStartTime')
            ->andWhere('loginTime <= :loginEndTime')
            ->andWhere('locked = :locked')
            ->andWhere('level >= :greatLevel')
            ->andWhere('verifiedMobile = :verifiedMobile')
            ->andWhere('type LIKE :type')
            ->andWhere('id IN ( :userIds)')
            ->andWhere('id NOT IN ( :excludeIds )');
            
        if (array_key_exists('hasVerifiedMobile', $conditions)) {
            $builder = $builder->andWhere('verifiedMobile != :verifiedMobileNull');
        }
        return $builder;
    }

    public function addUser($user)
    {
        $affected = $this->getConnection()->insert($this->table, $user);
        if ($affected <= 0) {
            throw $this->createDaoException('Insert user error.');
        }
        return $this->getUser($this->getConnection()->lastInsertId());
    }

    public function updateUser($id, $fields)
    {
        $this->getConnection()->update($this->table, $fields, array('id' => $id));
        return $this->getUser($id);
    }

    public function waveCounterById($id, $name, $number)
    {
        $names = array('newMessageNum', 'newNotificationNum');
        if (!in_array($name, $names)) {
            throw $this->createDaoException('counter name error');
        }
        $sql = "UPDATE {$this->table} SET {$name} = {$name} + ? WHERE id = ? LIMIT 1";
        return $this->getConnection()->executeQuery($sql, array($number, $id));
    }

    public function clearCounterById($id, $name)
    {
        $names = array('newMessageNum', 'newNotificationNum');
        if (!in_array($name, $names)) {
            throw $this->createDaoException('counter name error');
        }
        $sql = "UPDATE {$this->table} SET {$name} = 0 WHERE id = ? LIMIT 1";
        return $this->getConnection()->executeQuery($sql, array($id));
    }

    public function analysisRegisterDataByTime($startTime,$endTime)
    {
        $sql="SELECT count(id) as count, from_unixtime(createdTime,'%Y-%m-%d') as date FROM `{$this->table}` WHERE`createdTime`>=? AND `createdTime`<=? group by from_unixtime(`createdTime`,'%Y-%m-%d') order by date ASC ";
        return $this->getConnection()->fetchAll($sql, array($startTime, $endTime));
    }

    public function analysisUserSumByTime($endTime)
    {
         $sql="select date, count(*) as count from (SELECT from_unixtime(o.createdTime,'%Y-%m-%d') as date from user o where o.createdTime<=? ) dates group by dates.date order by date desc";
         return $this->getConnection()->fetchAll($sql, array($endTime));
    }

    public function findUsersCountByLessThanCreatedTime($endTime)
    {
        $sql="SELECT count(id) as count FROM `{$this->table}` WHERE  `createdTime`<=?  ";
        return $this->getConnection()->fetchColumn($sql, array($endTime));
    }
}